"""
存储爬取的数据到 mysql
"""

import pymysql
import re


class Storage:
    def __init__(self):
        self.conn = pymysql.connect(host='localhost', user='root', password='392790', database='mysql',
                                    charset='utf8', port=3306)
        self.cursor = self.conn.cursor()

    def create_table(self, key_table_name):
        sql1 = f'''
                DROP TABLE IF EXISTS WEIBO_KEY_CONTENT_{key_table_name};
                '''
        sql2 = f'''
                DROP TABLE IF EXISTS WEIBO_USER_INFO_{key_table_name};
                '''
        sql3 = f'''
                CREATE TABLE IF NOT EXISTS WEIBO_KEY_CONTENT_{key_table_name}(
                    id INT NOT NULL AUTO_INCREMENT COMMENT 'id',
                    search_key VARCHAR(100) NOT NULL COMMENT '关键字',
                    author VARCHAR(100) NOT NULL COMMENT '发布者',
                    author_id VARCHAR(100) NOT NULL COMMENT '发布者ID',
                    content VARCHAR(10000) NOT NULL COMMENT '微博内容',
                    forwarding_num bigint(20) NOT NULL COMMENT '转发数',
                    comment_num bigint(20) NOT NULL COMMENT '评论数', 
                    like_num bigint(20) NOT NULL COMMENT '点赞数',
                    release_time datetime NOT NULL COMMENT '发布时间',
                    platform VARCHAR(100) COMMENT '发布平台',
                    update_time datetime NOT NULL COMMENT '采集时间',
                    PRIMARY KEY (id,author_id)
                    ) COMMENT='微博内容表' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
                '''
        sql4 = f'''
                CREATE TABLE IF NOT EXISTS WEIBO_USER_INFO_{key_table_name}(
                    id INT NOT NULL AUTO_INCREMENT COMMENT 'id',
                    author VARCHAR(100) NOT NULL COMMENT '发布者',
                    author_id VARCHAR(100) NOT NULL COMMENT '发布者ID',
                    gender VARCHAR(100) COMMENT '发布者性别',
                    fans_num bigint(20) NOT NULL COMMENT '粉丝数', 
                    follow_num bigint(20) NOT NULL COMMENT '关注数',
                    weibo_total_num bigint(20) NOT NULL COMMENT '发布者总微博数', 
                    total_num bigint(20) NOT NULL COMMENT '发布者转评赞数', 
                    location VARCHAR(100) COMMENT '发布者地区', 
                    description VARCHAR(1000) COMMENT '发布者简介', 
                    tags VARCHAR(1000) COMMENT '发布者标签',
                    update_time datetime NOT NULL COMMENT '采集时间',
                    PRIMARY KEY (id,author_id)
                    ) COMMENT='微博用户表' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
                '''
        cursor = self.cursor
        try:
            cursor.execute(sql1)
            print(f'''微博内容表删除成功''')
            cursor.execute(sql2)
            print(f'''微博用户表删除成功''')
            cursor.execute(sql3)
            print(f'''微博内容表创建成功''')
            cursor.execute(sql4)
            print(f'''微博用户表创建成功''')
            self.conn.commit()
        except Exception as e:
            print(e)
            self.conn.rollback()
        self.conn.close()

    def insert_data(self, data_content, data_user, key_table_name):
        cursor = self.cursor
        data_content = self.deal_dict(data_content)
        for data in data_content:
            # print(f'''内容信息: {data}''')
            sql1 = f'''
                        INSERT INTO WEIBO_KEY_CONTENT_{key_table_name}
                        (search_key,author,author_id,content,forwarding_num,comment_num,like_num,release_time,platform,update_time)
                        VALUES('{data['search_key']}','{data['author']}','{data['author_id']}','{data['content']}',
                        {data['forwarding_num']},{data['comment_num']},{data['like_num']},'{data['release_time']}',
                        '{data['platform']}','{data['update_time']}');'''
            try:
                cursor.execute(sql1)
                # print(f'''微博内容表插入数据成功''')
            except Exception as e:
                print(e)
                self.conn.rollback()

        data_user = self.deal_dict(data_user)
        for data in data_user:
            # print(f'''用户信息: {data}''')
            sql2 = f'''
                    INSERT INTO WEIBO_USER_INFO_{key_table_name}
                    (author,author_id,gender,fans_num,follow_num,weibo_total_num,total_num,location,description,tags,update_time)
                    VALUES('{data['author']}','{data['author_id']}','{data['gender']}',{data['fans_num']},
                    {data['follow_num']},{data['weibo_total_num']},{data['total_num']},
                    '{data['location']}','{data['description']}','{data['tags']}','{data['update_time']}');'''
            try:
                cursor.execute(sql2)
                # print(f'''微博用户表插入数据成功''')
                self.conn.commit()
            except Exception as e:
                print(e)
                self.conn.rollback()
        self.conn.close()

    def deal_dict(self, data):
        for i in range(len(data['author_id'])):
            new_data = {}
            for k in data.keys():
                if k in ['search_key', 'update_time']:
                    new_data[k] = data[k]
                else:
                    if k in ['description', 'content', 'tags', 'location', 'platform', 'author']:
                        new_data[k] = re.sub(r"[\\x{10000}\-\\x{10FFFF}]", '', data[k][i])
                    else:
                        new_data[k] = data[k][i]
            yield new_data

